package com.ibsol.database
{
	import com.ibsol.model.Transaction;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class TransactionDAO
	{
		
		private var _tourixDBConn:SQLConnection;
		
		public function TransactionDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Load transaction records according to a time period and user id
		 */
		public function loadTransactionByUser(startDate:String, endDate:String, uid:uint, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT t.*, p.name, (c.firstName||' '||c.lastName) as cName, c.mobile " + 
					" FROM trans as t, package as p, customer as c " + 
					" WHERE t.offlineOperator != 'DEL' and p.offlineOperator != 'DEL' and c.offlineOperator != 'DEL' and " + 
					" t.packageid = p.id and t.cid = c.id and tranTime>='" + startDate + "' and tranTime <='" + endDate + "' and t.uid = " + uid;
			trace(sqlStr.text);
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Load all transaction according to a time period
		 */
		public function loadAllTransaction(startDate:String, endDate:String, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT t.*, p.name, (c.firstName||' '||c.lastName) as cName " + 
					" FROM trans as t, package as p, customer as c " + 
					" WHERE t.offlineOperator != 'DEL' and " + 
					" t.packageid = p.id and t.cid = c.id and tranTime>='" + startDate + "' and tranTime <='" + endDate + "'";
			trace(sqlStr.text);
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null? sqlErrorHandler : faultHandler);
			sqlStr.execute();
			
		}
		/**
		 * Insert a transcation record into the local database
		 */
		public function insertTransaction(tran:Transaction, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO trans (packageid, uid, departureDate, numCustomer, cid, remarks, price, discount, refNo, tranTime, offlineOperator, updateTime) " + 
					" values (" + tran.packageid + "," + tran.uid + ",'" + tran.departureDate + "'," + tran.numCustomer + "," + tran.cid + ",'" + 
					tran.remarks + "'," + tran.price + "," + tran.discount + ",'" + tran.refNo + "','" + tran.tranTime + "','NEW','T')";
			trace(sqlStr.text);
			sqlStr.addEventListener(SQLEvent.RESULT, function (event:SQLEvent):void
			{
				tran.id = sqlStr.getResult().lastInsertRowID;
				if(resultHandler != null)
					resultHandler.call(this, tran.id);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		 /**
		 * Converts array object to Transaction array collection
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int = 0; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Converts array object to Transaction array collection.
		 * @param:	o:Object	database return object
		 * @return:	Transaction object
		 */
		 public function typeObject(o:Object):Transaction
		 {
		 	var t:Transaction = new Transaction();
		 	t.id = o.id;
		 	t.packageid = o.packageid;
		 	t.uid = o.uid;
		 	t.departureDate = o.departureDate;
		 	t.numCustomer = o.numCustomer;
		 	t.cid = o.cid;
		 	t.price = o.price;
		 	t.discount = o.discount;
		 	t.remarks = o.remarks;
		 	t.refNo = o.refNo;
		 	t.tranTime = o.tranTime;
		 	t.offlineOpeartor = o.offlineOperator;
		 	t.updateTime = o.updateTime;
		 	t.pairid = o.pairid;
		 	t.customerName = o.cName;
		 	t.packageName = o.name;
		 	t.mobile = o.mobile;
		 	return t;
		 }
		 /**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}

	}
}